# database.py
import os
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import json

# 数据库配置
basedir = os.path.abspath(os.path.dirname(__file__))
DATABASE_PATH = os.path.join(basedir, 'hydroponic_system.db')

# SQLAlchemy实例
db = SQLAlchemy()

def init_db(app):
    """初始化数据库"""
    app.config['SQLALCHEMY_DATABASE_URI'] = f'sqlite:///{DATABASE_PATH}'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    db.init_app(app)
    
    # 创建所有表
    with app.app_context():
        db.create_all()
    print(f"✅ 数据库已初始化: {DATABASE_PATH}")

# 传感器数据模型
class SensorData(db.Model):
    __tablename__ = 'sensor_data'
    
    id = db.Column(db.Integer, primary_key=True)
    co2 = db.Column(db.Integer, nullable=False)
    ph = db.Column(db.Float, nullable=False)
    tds = db.Column(db.Float, nullable=False)
    dissolved_oxygen = db.Column(db.Float, nullable=False)
    temperature = db.Column(db.Float, nullable=False)
    humidity = db.Column(db.Integer, nullable=False)
    light_intensity = db.Column(db.Float, nullable=False)
    pest_detection = db.Column(db.Integer, default=0)
    timestamp = db.Column(db.DateTime, default=datetime.now, index=True)
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'co2': self.co2,
            'ph': self.ph,
            'tds': self.tds,
            'dissolved_oxygen': self.dissolved_oxygen,
            'temperature': self.temperature,
            'humidity': self.humidity,
            'light_intensity': self.light_intensity,
            'pest_detection': self.pest_detection,
            'timestamp': self.timestamp.strftime("%Y-%m-%d %H:%M:%S")
        }

# 设备状态历史模型
class DeviceStatusHistory(db.Model):
    __tablename__ = 'device_status_history'
    
    id = db.Column(db.Integer, primary_key=True)
    device = db.Column(db.String(50), nullable=False, index=True)
    status = db.Column(db.Integer, nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.now, index=True)
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'device': self.device,
            'status': self.status,
            'timestamp': self.timestamp.strftime("%Y-%m-%d %H:%M:%S")
        }

# 警报记录模型
class AlertLog(db.Model):
    __tablename__ = 'alert_log'
    
    id = db.Column(db.Integer, primary_key=True)
    parameter = db.Column(db.String(50), nullable=False, index=True)
    value = db.Column(db.Float, nullable=False)
    threshold = db.Column(db.Float, nullable=False)
    alert_type = db.Column(db.String(20), nullable=False)  # 'low', 'high', 'pest'
    message = db.Column(db.String(200), nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.now, index=True)
    resolved = db.Column(db.Boolean, default=False)
    resolved_at = db.Column(db.DateTime, nullable=True)
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'parameter': self.parameter,
            'value': self.value,
            'threshold': self.threshold,
            'alert_type': self.alert_type,
            'message': self.message,
            'timestamp': self.timestamp.strftime("%Y-%m-%d %H:%M:%S"),
            'resolved': self.resolved,
            'resolved_at': self.resolved_at.strftime("%Y-%m-%d %H:%M:%S") if self.resolved_at else None
        }

# 图像记录模型
class ImageRecord(db.Model):
    __tablename__ = 'image_records'
    
    id = db.Column(db.Integer, primary_key=True)
    image_data = db.Column(db.Text, nullable=False)  # 存储base64编码的图像数据
    timestamp = db.Column(db.DateTime, default=datetime.now, index=True)
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'image_data': self.image_data,
            'timestamp': self.timestamp.strftime("%Y-%m-%d %H:%M:%S")
        }

# 系统设置模型
class SystemSettings(db.Model):
    __tablename__ = 'system_settings'
    
    id = db.Column(db.Integer, primary_key=True)
    setting_key = db.Column(db.String(100), unique=True, nullable=False, index=True)
    setting_value = db.Column(db.Text, nullable=False)
    description = db.Column(db.String(200))
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'setting_key': self.setting_key,
            'setting_value': self.setting_value,
            'description': self.description,
            'updated_at': self.updated_at.strftime("%Y-%m-%d %H:%M:%S")
        }

# 数据库操作类
class DatabaseManager:
    def __init__(self, app=None):
        if app:
            self.init_app(app)
    
    def init_app(self, app):
        """初始化应用"""
        init_db(app)
        self.app = app
    
    def save_sensor_data(self, sensor_data):
        """保存传感器数据"""
        with self.app.app_context():
            try:
                record = SensorData(
                    co2=sensor_data['co2'],
                    ph=sensor_data['ph'],
                    tds=sensor_data['tds'],
                    dissolved_oxygen=sensor_data['dissolved_oxygen'],
                    temperature=sensor_data['temperature'],
                    humidity=sensor_data['humidity'],
                    light_intensity=sensor_data['light_intensity'],
                    pest_detection=sensor_data.get('pest_detection', 0)
                )
                db.session.add(record)
                db.session.commit()
                return record.id
            except Exception as e:
                db.session.rollback()
                print(f"❌ 保存传感器数据失败: {e}")
                return None
    
    def save_device_status(self, device, status):
        """保存设备状态变化"""
        with self.app.app_context():
            try:
                record = DeviceStatusHistory(
                    device=device,
                    status=status
                )
                db.session.add(record)
                db.session.commit()
                return record.id
            except Exception as e:
                db.session.rollback()
                print(f"❌ 保存设备状态失败: {e}")
                return None
    
    def save_alert(self, alert_data):
        """保存警报记录"""
        with self.app.app_context():
            try:
                record = AlertLog(
                    parameter=alert_data['parameter'],
                    value=alert_data['value'],
                    threshold=alert_data['threshold'],
                    alert_type=alert_data['type'],
                    message=alert_data['message']
                )
                db.session.add(record)
                db.session.commit()
                return record.id
            except Exception as e:
                db.session.rollback()
                print(f"❌ 保存警报记录失败: {e}")
                return None
    
    def save_image_record(self, image_data):
        """保存图像记录"""
        with self.app.app_context():
            try:
                record = ImageRecord(
                    image_data=image_data
                )
                db.session.add(record)
                db.session.commit()
                return record.id
            except Exception as e:
                db.session.rollback()
                print(f"❌ 保存图像记录失败: {e}")
                return None
    
    def get_sensor_data_history(self, hours=24, limit=1000):
        """获取传感器历史数据"""
        with self.app.app_context():
            try:
                from datetime import datetime, timedelta
                time_threshold = datetime.now() - timedelta(hours=hours)
                
                records = SensorData.query.filter(
                    SensorData.timestamp >= time_threshold
                ).order_by(SensorData.timestamp.desc()).limit(limit).all()
                
                return [record.to_dict() for record in records]
            except Exception as e:
                print(f"❌ 获取传感器历史数据失败: {e}")
                return []
    
    def get_alerts(self, unresolved_only=False, hours=24, limit=100):
        """获取警报记录"""
        with self.app.app_context():
            try:
                from datetime import datetime, timedelta
                time_threshold = datetime.now() - timedelta(hours=hours)
                
                query = AlertLog.query.filter(
                    AlertLog.timestamp >= time_threshold
                )
                
                if unresolved_only:
                    query = query.filter(AlertLog.resolved == False)
                
                records = query.order_by(AlertLog.timestamp.desc()).limit(limit).all()
                return [record.to_dict() for record in records]
            except Exception as e:
                print(f"❌ 获取警报记录失败: {e}")
                return []
    
    def resolve_alert(self, alert_id):
        """解决警报"""
        with self.app.app_context():
            try:
                alert = AlertLog.query.get(alert_id)
                if alert:
                    alert.resolved = True
                    alert.resolved_at = datetime.now()
                    db.session.commit()
                    return True
                return False
            except Exception as e:
                db.session.rollback()
                print(f"❌ 解决警报失败: {e}")
                return False
    
    def get_system_setting(self, key, default=None):
        """获取系统设置"""
        with self.app.app_context():
            try:
                setting = SystemSettings.query.filter_by(setting_key=key).first()
                if setting:
                    return setting.setting_value
                return default
            except Exception as e:
                print(f"❌ 获取系统设置失败: {e}")
                return default
    
    def set_system_setting(self, key, value, description=None):
        """设置系统设置"""
        with self.app.app_context():
            try:
                setting = SystemSettings.query.filter_by(setting_key=key).first()
                if setting:
                    setting.setting_value = value
                    if description:
                        setting.description = description
                else:
                    setting = SystemSettings(
                        setting_key=key,
                        setting_value=value,
                        description=description
                    )
                    db.session.add(setting)
                db.session.commit()
                return True
            except Exception as e:
                db.session.rollback()
                print(f"❌ 设置系统设置失败: {e}")
                return False

# 创建全局数据库管理器实例
db_manager = DatabaseManager()